import random

from core.models import Rating, Restaurant, Sale, Staff, StaffRestaurant
from django.contrib.auth.models import User
from django.utils import timezone
from django.db.models import Avg, Value, CharField, Sum, Count, F, Q, Case, When, Min, Max
from django.db.models.functions import Lower, Concat, Coalesce
import itertools


def run():
    types = Restaurant.TypeChoices
    italian = Restaurant.TypeChoices.ITALIAN
    restaurants = Restaurant.objects.annotate(
        is_italian=Case(
            default=F('restaurant_type') == italian,
        )
    )
    print(restaurants.filter(is_italian=True))
    print('-----------------------------')
    restaurants = Restaurant.objects.annotate(nsales=Count('sales'))
    restaurants = restaurants.annotate(
        is_popular=Case(
            When(nsales__gt=8, then=True),
            default=False,
        )
    )
    print(restaurants.values('name', 'nsales', 'is_popular'))
    print('---===============')
    restaurants = Restaurant.objects.annotate(
        avg_ratings=Avg('ratings__rating'),
        num_ratings=Count('ratings__pk')
    )
    print(restaurants.values('avg_ratings', 'num_ratings'))
    print(
        restaurants.annotate(
            highly_rated=Case(
                When(avg_ratings__gt=5, num_ratings__gt=1, then=True),
                default=False
            )
        ).filter(highly_rated=True)
    )
    restaurants = restaurants.annotate(
        rating_bucket=Case(
            When(avg_ratings__gt=3.5, then=Value('good')),
            When(avg_ratings__range=(1.5, 3.5), then=Value('average')),
            When(avg_ratings__lt=1.5, then=Value('poor'))
        )
    )
    print(restaurants.values('rating_bucket', 'avg_ratings'))
    restaurants = Restaurant.objects.annotate(
        continent=Case(
            When(Q(restaurant_type=types.ITALIAN) | Q(restaurant_type=types.GREEK), then=Value('Europe')),
            When(Q(restaurant_type=types.INDIAN) | Q(restaurant_type=types.CHINESE), then=Value('Asia')),
            When(Q(restaurant_type=types.MEXICAN), then=Value('North America')),
            default=Value('N/A'),
        )
    )
    print(restaurants.values('continent', 'name'))
    print(restaurants.filter(continent='Asia').values('continent', 'name'))
    # aggregating total sales over each 10 day period, starting from the first sale up until the last
    first_sale = Sale.objects.aggregate(first_sale_date=Min('datetime'))['first_sale_date']
    last_sale = Sale.objects.aggregate(last_sale_date=Max('datetime'))['last_sale_date']

    # generate a list of dates, each 10 days apart.
    dates = []
    count = itertools.count()

    while (dt := first_sale + timezone.timedelta(days=10*next(count))) <= last_sale:
        dates.append(dt)
    print(dates)

    whens = [
        When(datetime__date__range=(dt, dt + timezone.timedelta(days=10)), then=Value(dt.date()))
        for dt in dates
    ]

    case = Case(
        *whens,
        output_field=CharField()
    )
    """
    SELECT CASE
           WHEN ("core_sale"."datetime")::date BETWEEN '2024-04-13'::date AND '2024-04-23'::date THEN '2024-04-13'::date
           WHEN ("core_sale"."datetime")::date BETWEEN '2024-04-23'::date AND '2024-05-03'::date THEN '2024-04-23'::date
           WHEN ("core_sale"."datetime")::date BETWEEN '2024-05-03'::date AND '2024-05-13'::date THEN '2024-05-03'::date
           WHEN ("core_sale"."datetime")::date BETWEEN '2024-05-13'::date AND '2024-05-23'::date THEN '2024-05-13'::date
           WHEN ("core_sale"."datetime")::date BETWEEN '2024-05-23'::date AND '2024-06-02'::date THEN '2024-05-23'::date
           ELSE NULL END         AS "daterange",
       SUM("core_sale"."income") AS "total_sales"
    FROM "core_sale"
    GROUP BY "daterange"
    LIMIT 21;
    """
    sales = Sale.objects.annotate(
        daterange=case
    ).values('daterange').annotate(total_sales=Sum('income'))
    print(sales)


